package com.metacube.metaconnect.model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class UserInformationDAO {

	private Connection conn;

	public UserInformationDAO() {
		conn = CreateConnectionToDatabase.createConnection();
	}

	/**
	 * Register user
	 * 
	 * @param form
	 *            user sign_up form bean object
	 * @return true if user is signed up successfully else return false
	 */
	public boolean insertUserInfoStep1(SignUpStep1Form form) {

		Connection conn = CreateConnectionToDatabase.createConnection();

		String insertSQL = "update userinformation "
				+ "set name = ?, password = ?, gender = ?, is_active = ? "
				+ "where email_id = ?;";

		PreparedStatement pstmt = null;

		try {

			pstmt = conn.prepareStatement(insertSQL);

			pstmt.setString(1, (form.getName().charAt(0) + "").toUpperCase()
					+ form.getName().substring(1));
			pstmt.setString(2, form.getPassword());
			pstmt.setString(3, form.getGender());
			pstmt.setBoolean(4, true);
			pstmt.setString(5, form.getEmail());

			int rowAffected = pstmt.executeUpdate();

			if (rowAffected == 1)
				return true;

			else
				return false;
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * insert details of new user
	 * 
	 * @param form
	 *            object of form bean of user sign-up page step2
	 * @param email
	 *            email_id of the user
	 * @param dob
	 *            date of birth of user
	 * @param imgName
	 *            image name of the user
	 * @return
	 */
	public boolean insertUserInfoStep2(SignUpStep2Form form, String email,
			Date dob, String imgName) {

		Connection conn = CreateConnectionToDatabase.createConnection();

		String insertSQL = "update userinformation "
				+ "set project_name = ?, designation = ?, contact_number = ?, date_of_birth=?,profile_picture_url=? "
				+ "where email_id = ?;";

		PreparedStatement pstmt = null;

		try {

			pstmt = conn.prepareStatement(insertSQL);

			pstmt.setString(1, form.getProject_name());
			pstmt.setString(2, form.getDesignation());
			pstmt.setString(3, form.getContact_number());
			pstmt.setDate(4, dob);
			pstmt.setString(5, imgName);
			pstmt.setString(6, email);

			int rowAffected = pstmt.executeUpdate();

			if (rowAffected == 1)
				return true;
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	/**
	 * change profile_picture of the user
	 * 
	 * @param update
	 *            object of form bean Of File Update form
	 * @param imgName
	 *            name of the image
	 * @param email
	 *            email_id of the user
	 * @return
	 */
	public boolean updateFile(FileUpdate update, String imgName, String email) {

		Connection conn = CreateConnectionToDatabase.createConnection();

		String updateSQL = "update userinformation "
				+ "set profile_picture_url=? " + "where email_id = ?;";

		PreparedStatement pstmt = null;

		try {

			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, imgName);
			pstmt.setString(2, email);

			int rowAffected = pstmt.executeUpdate();
			if (rowAffected == 1)
				return true;
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	/**
	 * checks whether user is already have an account
	 * 
	 * @param email
	 *            email_id of the user
	 * @return true if user have account else return false
	 * @throws SQLException
	 */
	public boolean alreadyActivate(String email) throws SQLException {
		boolean result = false;

		String query = "select * from userinformation where email_id = ? and is_active=?";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setString(1, email);
			ps.setBoolean(2, true);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = true;
			}
		} catch (SQLException e) {
			throw e;
		}
		return result;
	}

	/**
	 * validate credentials of user
	 * 
	 * @param email
	 *            email_id/userName of user
	 * @param password
	 *            password of the user
	 * @return true if credentials are valid else returns false
	 * @throws SQLException
	 */
	public boolean login(String email, String password) throws SQLException {

		boolean result = false;

		String query = "select email_id from userinformation where email_id = ? and password = ? and is_active=?";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setString(1, email);
			ps.setString(2, password);
			ps.setBoolean(3, true);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = true;
			}
		} catch (SQLException e) {
			System.out.println("Error:" + e.getMessage());
			throw e;
		}

		return result;
	}

	/**
	 * to get password of user
	 * 
	 * @param email
	 *            email_id of the user
	 * @return password of user if exists else returns null
	 * @throws SQLException
	 */
	public String getActiveUserPassword(String email) throws SQLException {
		String password = null;
		String query = "select password from userinformation where email_id = ? and is_active=?";
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);
			ps.setString(1, email);
			ps.setBoolean(2, true);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				password = rs.getString("password");
			}
		} catch (SQLException e) {
			throw e;
		}

		return password;
	}

	/**
	 * 
	 * @param email
	 *            email_id of the user
	 * @return list containing information of user
	 */
	public ArrayList getuserInfo(String email) {
		ArrayList userInfo = new ArrayList();

		String query = "select * from userinformation where email_id = ? and is_active=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(query);
			ps.setString(1, email);
			ps.setBoolean(2, true);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println("mohit1");
				userInfo.add(rs.getString("name"));
				userInfo.add(rs.getString("gender"));
				userInfo.add(rs.getString("project_name"));
				userInfo.add(rs.getString("designation"));
				userInfo.add(rs.getString("profile_picture_url"));
			}
		} catch (SQLException e) {

		}

		return userInfo;
	}

	/**
	 * updates the details of user
	 * 
	 * @param form
	 *            object of update form bean
	 * @param date_of_birth
	 *            updated date_of birth of user
	 * @return true if updated successfully else returns false
	 */
	public boolean Update(UpdateProfileForm form, String date_of_birth) {

		String query = "update userinformation SET name=?, contact_number=?, project_name=?,"
				+ " designation=?, date_of_birth=? where email_id=?";

		PreparedStatement ps = null;

		try {

			ps = conn.prepareStatement(query);

			ps.setString(1, form.getName());
			ps.setString(2, form.getContact_number());
			ps.setString(3, form.getProject_name());
			ps.setString(4, form.getDesignation());
			ps.setString(5, date_of_birth);
			ps.setString(6, form.getEmail());

			int result = ps.executeUpdate();

			if (result == 1)
				return true;
		} catch (SQLException e) {
			System.out.println("Error" + e.getMessage());
		}
		return false;
	}

	/**
	 * search the users
	 * 
	 * @param name
	 *            name of the user to search
	 * @return list containg users that matches with provided name
	 */
	public ArrayList<String> searchUser(String name) {
		ArrayList<String> users = new ArrayList<String>();

		String query = "select name, profile_picture_url, email_id, date_of_birth, project_name, gender from userinformation "
				+ "where is_active=? and name like ?";

		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);

			ps.setBoolean(1, true);
			ps.setString(2, "%" + name + "%");

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				users.add(rs.getString("profile_picture_url"));
				users.add(rs.getString("name"));
				users.add(rs.getString("email_id"));
				users.add(rs.getString("date_of_birth"));
				users.add(rs.getString("project_name"));
				users.add(rs.getString("gender"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * checks whether activation code already exists inn the database
	 * 
	 * @param activationCode
	 *            activation_code to be checked
	 * @return true if activation_code present else returns false
	 */
	public boolean check(int activationCode) {

		// boolean result;
		ResultSet rs = null;

		Connection conn = CreateConnectionToDatabase.createConnection();
		if (conn == null) {
			return false;
		} else {
			String checkSQL = "SELECT activation_code from userinformation where activation_code = ?";
			PreparedStatement pstmt = null;
			try {
				pstmt = conn.prepareStatement(checkSQL);
				pstmt.setInt(1, activationCode);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;

	}

	/**
	 * insert details of user before sending an activation mail
	 * 
	 * @param emailId
	 *            email_id of the user
	 * @param activationCode
	 *            activation code sent to the user
	 * @return
	 */
	public boolean insertInactiveUserDetails(String emailId, int activationCode) {

		Connection conn = CreateConnectionToDatabase.createConnection();

		String deleteSQL = "delete from userinformation "
				+ "where email_id = ? and is_active = ?;";

		String insertSQL = "insert into userinformation "
				+ "(email_id, is_active, activation_code)" + "values(?,?,?);";

		PreparedStatement pstmt = null;
		try {

			pstmt = conn.prepareStatement(deleteSQL);

			pstmt.setString(1, emailId);
			pstmt.setBoolean(2, false);

			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, emailId);
			pstmt.setBoolean(2, false);
			pstmt.setInt(3, activationCode);

			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * to get email_id associated with activation code
	 * 
	 * @param activationCode
	 *            activation code sent to the user
	 * @return email_id of the user associated with passed activation code
	 */
	public String getEmailId(int activationCode) {

		String fetchSQL = "select email_id, is_active from userinformation "
				+ "where activation_code = ?";

		ResultSet rs = null;
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(fetchSQL);

			pstmt.setInt(1, activationCode);

			rs = pstmt.executeQuery();

			if (rs.next()) {
				String email = rs.getString("email_id");
				boolean isActive = rs.getBoolean("is_active");

				if (isActive) {
					return "active," + email;
				} else {
					return email;
				}
			} else {
				return "invalid activation code";
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * return details of the user
	 * 
	 * @param email
	 *            email_id of the user
	 * @return list containing details of user
	 */
	public ArrayList fetchDetails(String email) {

		Connection conn = CreateConnectionToDatabase.createConnection();
		ArrayList userDetails = new ArrayList();

		String fetchSQL = "select email_id,name,date_of_birth,contact_number,project_name,designation,gender "
				+ "from userinformation where email_id='" + email + "'";

		String email_id = "";
		String name = "";
		String password = "";
		Date date_of_birth;
		String contact_number;
		String project_name;
		String designation;
		String profile_picture_url = "";
		String gender = "";

		Statement stmt = null;
		ResultSet rs = null;

		try {

			stmt = conn.createStatement();

			rs = stmt.executeQuery(fetchSQL);

			while (rs.next()) {
				email_id = rs.getString(1);
				name = rs.getString(2);
				date_of_birth = rs.getDate(3);
				contact_number = rs.getString(4);
				project_name = rs.getString(5);
				designation = rs.getString(6);

				gender = rs.getString(7);
				if (email != null)
					userDetails.add(email);
				else
					userDetails.add("");
				if (name != null)
					userDetails.add(name);
				else
					userDetails.add("");
				if (date_of_birth != null)
					userDetails.add(date_of_birth);
				else
					userDetails.add("");
				userDetails.add(contact_number + "");
				if (project_name != null)
					userDetails.add(project_name);
				else
					userDetails.add("");
				if (designation != null)
					userDetails.add(designation);
				else
					userDetails.add("");
				if (gender != null)
					userDetails.add(gender);
				else
					userDetails.add("");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userDetails;

	}
	
	public String getProfilePicUrl(String email)
	{
		String query="select profile_picture_url, gender from userinformation where email_id=?";
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(query);
			ps.setString(1, email);
			ResultSet rs = ps.executeQuery();
			if(rs.next())
				return "Upload/"+rs.getString("profile_picture_url");
			else if(rs.getString("gender").equals("Male"))
				return "Upload/male.jpg";
			else
				return "Upload/female.jpg";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
